﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
//using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
/// <summary>
/// Descripción breve de DB
/// </summary>
public class DB
{
	public DB()
	{
		
	}
    public static SqlConnection OpenConnection()
    {
        SqlConnection cnn = null;
        try
        {
            cnn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=megahealth;Integrated Security=True;Pooling=False");
            cnn.Open();
        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return cnn;
    }

    public static RE_GenericBean Autenticar(string usu_id)
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean result = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT   usuario, contraseña, nivel   FROM   usuario   WHERE   (UPPER(usuario) = @usuID) ";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usu_id.ToUpper();
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_GenericBean();
                if (!reader.IsDBNull(0)) result.strC1 = reader.GetString(0).ToUpper();
                if (!reader.IsDBNull(1)) result.strC2 = reader.GetString(1).ToUpper();
                if (!reader.IsDBNull(2)) result.intC1 = reader.GetInt32(2);
            }
        }
        catch (Exception e)
        {
            Utility.writeError(DateTime.Now.ToString() + " " + e.Message.ToString() + " " + e.StackTrace);
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static System.Data.DataSet getCategorias()
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("id");
        result.Tables["Tabla"].Columns.Add("nombre");
        result.Tables["Tabla"].Columns.Add("descripcion");



        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  id, nombre, descripcion FROM categoria";

            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//ID
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);//descripcion



                object[] objArr = { rgb.strC1, rgb.strC2, rgb.strC3 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();
        }
        catch (Exception e)
        {
            Utility.writeError(e.StackTrace);
            return null;
        }
        return result;
    }

    public static DataSet getProductosporCategoria(string cat)
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("id");
        result.Tables["Tabla"].Columns.Add("nombre");
        result.Tables["Tabla"].Columns.Add("descripcion");
        result.Tables["Tabla"].Columns.Add("beneficios");
        result.Tables["Tabla"].Columns.Add("modoempleo");
        result.Tables["Tabla"].Columns.Add("foto");
        result.Tables["Tabla"].Columns.Add("ingredientes");
        result.Tables["Tabla"].Columns.Add("recomendacion");
        result.Tables["Tabla"].Columns.Add("preciodistq"); 
        result.Tables["Tabla"].Columns.Add("preciodistd");
        result.Tables["Tabla"].Columns.Add("preciofinq");
        result.Tables["Tabla"].Columns.Add("preciofind");
        result.Tables["Tabla"].Columns.Add("categoria");
        result.Tables["Tabla"].Columns.Add("presentacion");



        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  id, nombre, descripcion, beneficios, modoempleto, foto, ingredientes, recomendaciones, convert(varchar,preciodistq), convert(varchar, preciodistd),convert(varchar, preciofinq), convert(varchar, preciofind), categoria, presentacion FROM producto where categoria like @cat";
            comm.Parameters.Add("@cat", SqlDbType.VarChar).Value = cat;
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//ID
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);//descripcion
                if (!reader.IsDBNull(3)) rgb.strC4 = reader.GetString(3);//beficios
                if (!reader.IsDBNull(4)) rgb.strC5 = reader.GetString(4);//modoempleo
                if (!reader.IsDBNull(5)) rgb.strC6 = reader.GetString(5);//foto
                if (!reader.IsDBNull(6)) rgb.strC7 = reader.GetString(6);//ingredientes
                if (!reader.IsDBNull(7)) rgb.strC8 = reader.GetString(7);//recomendacion
                if (!reader.IsDBNull(8)) rgb.strC9 = reader.GetString(8);//preciodistq
                if (!reader.IsDBNull(9)) rgb.strC10 = reader.GetString(9);//preciodistd
                if (!reader.IsDBNull(10)) rgb.strC11 = reader.GetString(10);//preciofinq
                if (!reader.IsDBNull(11)) rgb.strC12 = reader.GetString(11);//preciofind
                if (!reader.IsDBNull(12)) rgb.strC13 = reader.GetString(12);//categoria
                if (!reader.IsDBNull(13)) rgb.strC14 = reader.GetString(13);//presentacion
             



                object[] objArr = { rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8, rgb.strC9, rgb.strC10, rgb.strC11, rgb.strC12, rgb.strC13, rgb.strC14};
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();
        }
        catch (Exception e)
        {
            Utility.writeError(e.Message);
            return null;
        }
        return result;
        
    }

    public static RE_GenericBean getProducto(string pro_id)
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean result = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT   id, nombre, descripcion, beneficios, modoempleto, foto, ingredientes, recomendaciones, CONVERT(varchar, preciodistq) AS PDQ, CONVERT(varchar, preciodistd) AS PDD, CONVERT(varchar, preciofinq) AS PPQ, CONVERT(varchar, preciofind) AS PPD, categoria, presentacion   FROM   producto   WHERE   (id = @proid) ";
            comm.Parameters.Add("@proid", SqlDbType.VarChar).Value = pro_id.ToUpper();
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_GenericBean();
                if (!reader.IsDBNull(0)) result.strC1 = reader.GetString(0);// id
                if (!reader.IsDBNull(1)) result.strC2 = reader.GetString(1);// nombre
                if (!reader.IsDBNull(2)) result.strC3 = reader.GetString(2);// descripcion
                if (!reader.IsDBNull(3)) result.strC4 = reader.GetString(3);// beneficios
                if (!reader.IsDBNull(4)) result.strC5 = reader.GetString(4);// modoempleo
                if (!reader.IsDBNull(5)) result.strC6 = reader.GetString(5);// foto
                if (!reader.IsDBNull(6)) result.strC7 = reader.GetString(6);// ingredientes
                if (!reader.IsDBNull(7)) result.strC8 = reader.GetString(7);// recomendaciones
                if (!reader.IsDBNull(8)) result.strC9 = reader.GetString(8);// precio dist. quetzal
                if (!reader.IsDBNull(9)) result.strC10 = reader.GetString(9);// precio dist. dolar
                if (!reader.IsDBNull(10)) result.strC11 = reader.GetString(10);// precio publico quetzal
                if (!reader.IsDBNull(11)) result.strC12 = reader.GetString(11);// precio publico dolar
                if (!reader.IsDBNull(12)) result.strC13 = reader.GetString(12);// categoria
                if (!reader.IsDBNull(13)) result.strC14 = reader.GetString(13);// presentacion
            }
        }
        catch (Exception e)
        {
            Utility.writeError(DateTime.Now.ToString() + " " + e.Message.ToString() + " " + e.StackTrace);
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static DataSet getProductosBusqueda(string criterio)
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("id");
        result.Tables["Tabla"].Columns.Add("nombre");
        result.Tables["Tabla"].Columns.Add("descripcion");
        result.Tables["Tabla"].Columns.Add("beneficios");
        result.Tables["Tabla"].Columns.Add("modoempleo");
        result.Tables["Tabla"].Columns.Add("foto");
        result.Tables["Tabla"].Columns.Add("ingredientes");
        result.Tables["Tabla"].Columns.Add("recomendacion");
        result.Tables["Tabla"].Columns.Add("preciodistq");
        result.Tables["Tabla"].Columns.Add("preciofinq");
        result.Tables["Tabla"].Columns.Add("categoria");
        result.Tables["Tabla"].Columns.Add("presentacion");
        result.Tables["Tabla"].Columns.Add("existencia");


        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            //comm.CommandText = " SELECT   id, nombre, descripcion, beneficios, modoempleto, foto, ingredientes, recomendaciones, CONVERT(varchar, preciodistq) AS Expr1, CONVERT(varchar, preciofinq) AS Expr2, categoria, presentacion   FROM   producto   WHERE   (beneficios collate SQL_Latin1_General_Cp1_CI_AI LIKE @criterio) OR (recomendaciones collate SQL_Latin1_General_Cp1_CI_AI LIKE @criterio) ";
            comm.CommandText = " SELECT id, nombre, descripcion, beneficios, modoempleto, foto, ingredientes, recomendaciones, CONVERT(varchar, preciodistq) AS Expr1, CONVERT(varchar, preciofinq) AS Expr2, categoria, presentacion, existencia   FROM   producto   WHERE   " + criterio + "   ORDER BY nombre ";
            //comm.Parameters.Add("@criterio", SqlDbType.VarChar).Value = criterio;
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//ID
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);//descripcion
                if (!reader.IsDBNull(3)) rgb.strC4 = reader.GetString(3);//beficios
                if (!reader.IsDBNull(4)) rgb.strC5 = reader.GetString(4);//modoempleo
                if (!reader.IsDBNull(5)) rgb.strC6 = reader.GetString(5);//foto
                if (!reader.IsDBNull(6)) rgb.strC7 = reader.GetString(6);//ingredientes
                if (!reader.IsDBNull(7)) rgb.strC8 = reader.GetString(7);//recomendacion
                if (!reader.IsDBNull(8)) rgb.strC9 = reader.GetString(8);//preciodistq
                if (!reader.IsDBNull(9)) rgb.strC10 = reader.GetString(9);//preciofinq
                if (!reader.IsDBNull(10)) rgb.strC11 = reader.GetString(10);//categoria
                if (!reader.IsDBNull(11)) rgb.strC12 = reader.GetString(11);//presentacion
                if (!reader.IsDBNull(12)) rgb.intC1 = reader.GetInt32(12);//existencia

                object[] objArr = { rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8, rgb.strC9, rgb.strC10, rgb.strC11, rgb.strC12, rgb.intC1 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();
        }
        catch (Exception e)
        {
            Utility.writeError(e.Message);
            return null;
        }
        return result;

    }


    public static DataSet getProductosInventario()
    {
        DataSet result = new DataSet();
        result.Tables.Add("Tabla");
        result.Tables["Tabla"].Columns.Add("id");
        result.Tables["Tabla"].Columns.Add("nombre");
        result.Tables["Tabla"].Columns.Add("descripcion");
        result.Tables["Tabla"].Columns.Add("beneficios");
        result.Tables["Tabla"].Columns.Add("modoempleo");
        result.Tables["Tabla"].Columns.Add("foto");
        result.Tables["Tabla"].Columns.Add("ingredientes");
        result.Tables["Tabla"].Columns.Add("recomendacion");
        result.Tables["Tabla"].Columns.Add("preciodistq");
        result.Tables["Tabla"].Columns.Add("preciofinq");
        result.Tables["Tabla"].Columns.Add("categoria");
        result.Tables["Tabla"].Columns.Add("presentacion");
        result.Tables["Tabla"].Columns.Add("existencia");

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = " SELECT   producto.id, producto.nombre, producto.descripcion, producto.beneficios, producto.modoempleto, producto.foto, producto.ingredientes, producto.recomendaciones, CONVERT(varchar, producto.preciodistq) AS Expr1, CONVERT(varchar, producto.preciofinq) AS Expr2, categoria.nombre AS Expr3, producto.presentacion, producto.existencia   FROM   producto INNER JOIN  categoria ON producto.categoria = categoria.id   ORDER BY producto.nombre ";
            reader = comm.ExecuteReader();
            RE_GenericBean rgb = null;
            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//ID
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);//descripcion
                if (!reader.IsDBNull(3)) rgb.strC4 = reader.GetString(3);//beficios
                if (!reader.IsDBNull(4)) rgb.strC5 = reader.GetString(4);//modoempleo
                if (!reader.IsDBNull(5)) rgb.strC6 = reader.GetString(5);//foto
                if (!reader.IsDBNull(6)) rgb.strC7 = reader.GetString(6);//ingredientes
                if (!reader.IsDBNull(7)) rgb.strC8 = reader.GetString(7);//recomendacion
                if (!reader.IsDBNull(8)) rgb.strC9 = reader.GetString(8);//preciodistq
                if (!reader.IsDBNull(9)) rgb.strC10 = reader.GetString(9);//preciofinq
                if (!reader.IsDBNull(10)) rgb.strC11 = reader.GetString(10);//categoria
                if (!reader.IsDBNull(11)) rgb.strC12 = reader.GetString(11);//presentacion
                if (!reader.IsDBNull(12)) rgb.intC1 = reader.GetInt32(12);//existencia

                object[] objArr = { rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8, rgb.strC9, rgb.strC10, rgb.strC11, rgb.strC12, rgb.intC1 };
                result.Tables["Tabla"].Rows.Add(objArr);
            }
            conn.Close();
        }
        catch (Exception e)
        {
            Utility.writeError(e.Message);
            return null;
        }
        return result;

    }

    public static bool cargarArchivo(DataSet productos)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;

            for (int i = 0; i < productos.Tables[0].Rows.Count; i++)
            {
                int num = 0;
                string aux = productos.Tables[0].Rows[i]["transaccion"].ToString();
                bool bandera = int.TryParse(aux, out num);
                if (bandera == true)
                {
                    if (int.Parse(productos.Tables[0].Rows[i]["transaccion"].ToString()) == 1)
                    {
                        comm.CommandText = " SELECT  existencia FROM producto  WHERE  (id=@ide) ";
                        comm.Parameters.Add("@ide", SqlDbType.VarChar).Value = productos.Tables[0].Rows[i]["codigo"].ToString();
                        reader = comm.ExecuteReader();
                        int cantidad = 0;
                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(0)) cantidad = reader.GetInt32(0);//existencia
                        }
                        comm.Parameters.Clear();
                        reader.Close();

                        cantidad += int.Parse(productos.Tables[0].Rows[i]["cantidad"].ToString());

                        comm.CommandText = " UPDATE   producto   SET   existencia = @cantidad   WHERE   (id = @ide) ";
                        comm.Parameters.Add("@cantidad", SqlDbType.Int).Value = cantidad;
                        comm.Parameters.Add("@ide", SqlDbType.VarChar).Value = productos.Tables[0].Rows[i]["codigo"].ToString();
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                    else if (int.Parse(productos.Tables[0].Rows[i]["transaccion"].ToString()) == 0)
                    {
                        comm.CommandText = " SELECT  existencia FROM producto  WHERE  (id=@ide) ";
                        comm.Parameters.Add("@ide", SqlDbType.VarChar).Value = productos.Tables[0].Rows[i]["codigo"].ToString();
                        reader = comm.ExecuteReader();
                        int cantidad = 0;
                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(0)) cantidad = reader.GetInt32(0);//existencia
                        }
                        comm.Parameters.Clear();
                        reader.Close();

                        if (cantidad >= int.Parse(productos.Tables[0].Rows[i]["cantidad"].ToString()))
                            cantidad = cantidad - int.Parse(productos.Tables[0].Rows[i]["cantidad"].ToString());
                        else
                            cantidad = 0;

                        comm.CommandText = " UPDATE   producto   SET   existencia = @cantidad   WHERE   (id = @ide) ";
                        comm.Parameters.Add("@cantidad", SqlDbType.Int).Value = cantidad;
                        comm.Parameters.Add("@ide", SqlDbType.VarChar).Value = productos.Tables[0].Rows[i]["codigo"].ToString();
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                }
            }

            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean getCategoria(string id)
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean result = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT  id, nombre, descripcion FROM categoria   WHERE   (id = @catid) ";
            comm.Parameters.Add("@catid", SqlDbType.VarChar).Value = id.ToUpper();
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_GenericBean();
                if (!reader.IsDBNull(0)) result.strC1 = reader.GetString(0);// id
                if (!reader.IsDBNull(1)) result.strC2 = reader.GetString(1);// nombre
                if (!reader.IsDBNull(2)) result.strC3 = reader.GetString(2);// descripcion
            }
        }
        catch (Exception e)
        {
            Utility.writeError(DateTime.Now.ToString() + " " + e.Message.ToString() + " " + e.StackTrace);
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean getVenta(int id)
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean result = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT  convert(varchar,fecha_creacion), convert(varchar,fecha_confirmacion), usuario, estado, convert(varchar,monto), nota, etapa, convert(varchar,fecha_esperada), cliente  FROM venta   WHERE   (id = @id) ";
            comm.Parameters.Add("@id", SqlDbType.VarChar).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_GenericBean();
                if (!reader.IsDBNull(0)) result.strC1 = reader.GetString(0);// fecha_creacion
                if (!reader.IsDBNull(1)) result.strC2 = reader.GetString(1);// fecha_confirmacion
                if (!reader.IsDBNull(2)) result.strC6 = reader.GetString(2);// usuario
                if (!reader.IsDBNull(3)) result.intC2 = reader.GetInt32(3);// estado
                if (!reader.IsDBNull(4)) result.strC3 = reader.GetString(4);// monto
                if (!reader.IsDBNull(5)) result.strC4 = reader.GetString(5);// nota
                if (!reader.IsDBNull(6)) result.intC3 = reader.GetInt32(6);// etapa
                if (!reader.IsDBNull(7)) result.strC5 = reader.GetString(7);// fecha_esperada
                if (!reader.IsDBNull(8)) result.intC4 = reader.GetInt32(8);// cliente
                result.intC6 = id;
            }
        }
        catch (Exception e)
        {
            Utility.writeError(DateTime.Now.ToString() + " " + e.Message.ToString() + " " + e.StackTrace);
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean setVenta(RE_GenericBean re)
    {
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean result = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " insert into venta (fecha_creacion, fecha_confirmacion, usuario, estado, monto, nota, etapa, fecha_esperada, cliente) values (getdate(),@fco,@us,@es,@monto,@nota,@etapa,@fe,@cli) ";
           
            
            comm.Parameters.Add("@fco", SqlDbType.VarChar).Value = re.strC2;
            comm.Parameters.Add("@us", SqlDbType.VarChar).Value = re.strC7;
            comm.Parameters.Add("@es", SqlDbType.Int).Value = re.intC2;
            comm.Parameters.Add("@monto", SqlDbType.VarChar).Value = re.strC3;
            comm.Parameters.Add("@nota", SqlDbType.VarChar).Value = re.strC4;
            comm.Parameters.Add("@etapa", SqlDbType.Int).Value = re.intC3;
            comm.Parameters.Add("@fe", SqlDbType.VarChar).Value = re.strC5;
            comm.Parameters.Add("@cli", SqlDbType.Int).Value = re.strC6;
            comm.ExecuteNonQuery();


            comm.CommandText = "select max(id) from venta";
            reader = comm.ExecuteReader();
            if (reader.Read())
            {
                result = new RE_GenericBean();
                if (!reader.IsDBNull(0)) result.intC1 = reader.GetInt32(0);// id
            }
            reader.Close();
            comm.Parameters.Clear();

        }
        catch (Exception e)
        {
            Utility.writeError(DateTime.Now.ToString() + " " + e.Message.ToString() + " " + e.StackTrace);
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static DataSet getCliente(string criterio)
    {
        DataSet ds = new DataSet();
        ds.Tables.Add("Tabla");
        ds.Tables["Tabla"].Columns.Add("Dim ");
        ds.Tables["Tabla"].Columns.Add("Nombre ");
        ds.Tables["Tabla"].Columns.Add("Telefono(s)");
        ds.Tables["Tabla"].Columns.Add("Dim patrocinador");
        ds.Tables["Tabla"].Columns.Add("Nombre Patrocinador");
        ds.Tables["Tabla"].Columns.Add("Telefono Patrocinador");
        SqlConnection conn = null;
        SqlConnection conn2 = null;
        SqlCommand comm = null;
        SqlCommand comm2 = null;
        SqlDataReader reader = null;
        SqlDataReader reader2 = null;
        RE_GenericBean result = null;
        
        try
        {
            int criterioint = 0;
            if (Utility.isNumeric(criterio)) criterioint = int.Parse(criterio);

            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm2 = new SqlCommand();
            comm2.Connection = conn2;
            comm2.CommandType = CommandType.Text;
            comm.CommandText = " SELECT   dim, Upper(apellido)+' '+Upper(nombre), telefono, dimpat  FROM cliente   WHERE   (dim= @criterioint or  nombre like '" + criterio + "%' or apellido like '" + criterio + "%' or telefono like '%" + criterio + "%') ";
            comm.Parameters.Add("@criterio", SqlDbType.VarChar).Value = criterio;
            comm.Parameters.Add("@criterioint", SqlDbType.Int).Value = criterioint;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_GenericBean();
                if (!reader.IsDBNull(0)) result.intC1 = reader.GetInt32(0);// dim
                if (!reader.IsDBNull(1)) result.strC2 = reader.GetString(1);// nombres + apellido
                if (!reader.IsDBNull(2)) result.strC3 = reader.GetString(2);// telefono
                if (!reader.IsDBNull(3)) result.intC2 = reader.GetInt32(3);// dimpatro

                comm2.CommandText = " SELECT   dim, Upper(apellido)+' '+Upper(nombre), telefono, dimpat  FROM cliente   WHERE   (dim= @criterio ) ";
                comm2.Parameters.Add("@criterio", SqlDbType.VarChar).Value = result.intC2;
                reader2 = comm2.ExecuteReader();

                if (reader2.Read())
                {
                   
                    if (!reader2.IsDBNull(1)) result.strC4 = reader2.GetString(1);// apellidos+nombres pat
                    if (!reader2.IsDBNull(2)) result.strC5 = reader2.GetString(2);// telefono pat


                }
               
                object[] objArr = { result.intC1, result.strC2, result.strC3, result.intC2, result.strC4, result.strC5 };
                ds.Tables["Tabla"].Rows.Add(objArr);
                
            }
            
            
            
        }
        catch (Exception e)
        {
            Utility.writeError(DateTime.Now.ToString() + " " + e.Message.ToString() + " " + e.StackTrace);
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (reader2 != null)
                reader2.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return ds;
    }

    public static int updateVenta(RE_GenericBean obj)
    {
        throw new NotImplementedException();
    }
}
